Code Customization: Add Additional Where Clause to Table

Filter Data

Add Additional Where Clause to Table

Description

This example demonstrates how to customize the filters used to determine which records data is displayed in a Table component.

Variables

Database Table

Select the database table displayed in the page

Table Control

Select the table control

Filter Field

Select a database table column which will be used to filter content of the table

Applies to

TableControl class

Code

 
/// 
/// Override the CreateWhereClause of the table control to add a filter.
/// 
public override WhereClause CreateWhereClause() 
{
    WhereClause wc;
    
    //  Call the base.CreateWhereClause() which will include all
    //  the filter and search criteria selected by user.        
    wc = base.CreateWhereClause();
    
    //  If base.CreateWhereClause() returns nothing
    //  create a new instance of WhereClause.
    if ((wc == null))
    {
        wc = new WhereClause();
    }
	bool expandForeignKey = false;
	bool isCaseSensitive = false;   
    wc.iAND(${${Database Table}ClassName}.${Filter Field}, BaseClasses.Data.BaseFilter.ComparisonOperator.EqualsTo, "1", expandForeignKey, isCaseSensitive);
    
    // You can add additional WhereClause here      
    // Additional examples of adding WhereClause are shown below.
    //  Example 1:
    //  The resultant whereClause we want is:
    //  (base.CreateWhereClause() AND ( ShipCountry="usa" or ShipCountry="uk") ) 
    //  base.CreateWhereClause will contain 
    //  1. User selected filter criteria.
    //  2. User selected search criteria.    
    //  WhereClause myFilterClause = new WhereClause();
    //  myFilterClause.iOR(OrdersTable.ShipCountry, BaseClasses.Data.BaseFilter.ComparisonOperator.EqualsTo, "usa");
    //  myFilterClause.iOR(OrdersTable.ShipCountry, BaseClasses.Data.BaseFilter.ComparisonOperator.EqualsTo, "uk");
    //  wc.iAND(myFilterClause);        
    
    //  *************************************
    //  Example 2:
    //  The resultant whereClause we want is:
    //  (base.CreateWhereClause() OR ( ShipCountry="usa" and ShipCity="LA") )
    //  WhereClause myFilterClause = new WhereClause();
    //  myFilterClause.iAND(OrdersTable.ShipCountry, BaseClasses.Data.BaseFilter.ComparisonOperator.EqualsTo, "USA");
    //  myFilterClause.iAND(OrdersTable.ShipCity, BaseClasses.Data.BaseFilter.ComparisonOperator.EqualsTo, "LA");
    //  wc.iOR(myFilterClause);
    
    //  *************************************
    //  Example 3:
    //  The resultant whereClause we want is:
    //  (base.CreateWhereClause() AND ( OrderDate<= "2/3/1997" )    
    //  WhereClause myFilterClause = new WhereClause();
    //  myFilterClause.iAND(OrdersTable.OrderDate, BaseClasses.Data.BaseFilter.ComparisonOperator.Less_Than_Or_Equal, "2/3/1997");
    //  wc.iAND(myFilterClause);
    // ***************************************  
    return wc;
}